Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


PCTUSED

Once PCTFREE is reached, no new rows can be inserted into the data block until the space in the block has fallen below PCTUSED. Another feature of PCTUSED is that Oracle tries to keep a data block at least PCTUSED full before using new blocks.

A high PCTUSED value has the following effects:

  Decreases performance because you usually have more migrating and chained rows.
  Reduces space waste by filling the data block more completely.

A lower value for PCTUSED has the following effects:

  Performance is improved because of the drop in the number of migrated and chained rows.
  Less efficient space usage caused by more unused space in the data blocks.

Just as with PCTFREE, it is worth the effort to look at each table individually and determine a value for PCTUSED that more accurately fits your application. Doing so can improve your system’s performance.

A Review of the PCTFREE and PCTUSED Options

As with most tuning efforts, there is a tradeoff between space usage and performance. Here are a few things to keep in mind when you are adjusting the values for PCTFREE and PCTUSED:

  Because (100 – PCTFREE) and PCTUSED are used as high water and low water marks, the sum of the two cannot exceed 100. On the other hand, the sum does not have to equal 100.
  The closer the sum of the two values gets to 100, or the closer (100 – PCTFREE) is to PCTUSED, the more overhead is incurred and the more efficient space usage is.
  If the sum of PCTFREE and PCTUSED equals 100, Oracle attempts to keep exactly PCTFREE free space, increasing CPU usage.
  Fixed block overhead is not included in the calculation of PCTFREE and PCTUSED.
  A good compromise of performance and space is to keep approximately one row difference between PCTFREE and PCTUSED. For example, with a 100-byte row in a 2048-byte data block with 100 bytes of overhead, use the following formula:
Space = 2048 - 100 = 1948
Average Row = 100 or 5 % of the data block
( 100 - PCTFREE ) - PCTUSED = 5

A large difference between (100 – PCTFREE) and PCTUSED means more empty space. Leaving one row difference is usually sufficient.

Here are a few guidelines to follow when adjusting PCTFREE and PCTUSED:

  Update Activity, High Row Growth
If your application frequently uses updates that affect the size of rows, set PCTFREE fairly high and set PCTUSED fairly low. This arrangement allows for a large amount of space in the data blocks for row size growth. For example:
PCTFREE = 20-25
PCTUSED = 35-40
( 100 - PCTFREE _) - PCTUSED = 35 to 45
  Insert Activity, Small Row Growth
If most inserts are new rows and there is very little update with row growth, set PCTFREE low with set a moderate value for PCTUSED to avoid chaining of new rows. This arrangement allows new rows to be inserted into the data block until the point at which more insertions are likely to cause migration or chaining. When this point is reached, no more insertions occur until there is a fair amount of space left in the block; then inserting can resume.
PCTFREE = 5-10
PCTUSED = 50-60
( 100 - PCTFREE _) - PCTUSED = 30 to 45
  Performance Primary, Space Abundant
If performance is critical and you have plenty of space available, you can ensure that migration and chaining never occur by setting PCTFREE very high and PCTUSED extremely low. Although this can waste quite a bit of space, all chaining and migration should be avoided.
PCTFREE = 30
PCTUSED = 30
( 100 - PCTFREE _) - PCTUSED = 40
  Space Critical, Performance Secondary
If you are have very large tables or if you have moderate-sized tables and disk space is at a premium, set PCTFREE very low and PCTUSED very high. This arrangement ensures that you take maximum advantage of the available space. Note that you will see some performance loss caused by increased chaining and migration.
PCTFREE = 5
PCTUSED = 90
( 100 - PCTFREE _) - PCTUSED = 5

A Review of I/O Reduction Techniques

Reduction of I/O is important for performance because I/O is one of the slowest operations in the computer system. Caching data is many times faster than an access from disk. In fact, a read from memory can take place over 100,000 times faster than a read from disk.

You have seen that you can speed up I/O by separating the sequential and the random I/Os and separating data from indexes to get maximum concurrency. You have also seen how to speed up I/Os by avoiding them altogether—such as by reducing dynamic extensions and by avoiding migrated and chained rows.

As you see in later chapters, the speed of I/O from disk is a fixed value that you can work around only by caching, reducing contention, and avoiding I/Os if possible.

Tuning Rollback Segments

Another area of contention may be in the rollback segments. Rollback segments are constantly used during transaction processing; any delays caused by contention on rollback segments affect performance.

Rollback segments record transactional information that may be used in the event that the transaction should be rolled back. Rollback segments are also used to provide read consistency and are used for database recovery.

Read consistency allows a long-running transaction to always obtain the same data within the query. During the transaction, the data is consistent to a single point in time and does not change. Even though the data may have changed, and perhaps the DBWR may even have written it out, other transactions do not see those changes until a COMMIT has occurred. In fact, only transactions that start after this transaction has been committed see those changes.

Rollback segments must be carefully watched and can be tuned in several ways. It is important not only to size the rollback segments correctly but also to create the proper number of rollback segments and properly distribute them according to the number of user processes that require them. The next section discusses how the rollback segments operate.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.